package top.went.db.dao;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import top.went.pojo.CustomerEntity;
import top.went.pojo.SaleOppEntity;
import top.went.pojo.UserEntity;

import java.util.List;

public interface SaleOppDao extends JpaRepository<SaleOppEntity, Long> {

    //搜寻某个客户对应的所有机会
    public List<SaleOppEntity> findAllByTbCustomerByCusIdAndIsDelete(CustomerEntity customerEntity, Long isDelete);

    //加载
    public SaleOppEntity findAllByOppIdAndIsDelete(Long oppId, Long isDelete);
    //加载所有
    public List<SaleOppEntity> findAllByIsDelete(Long isDelete);
    //加载所有（分页）
    public List<SaleOppEntity> findAllByIsDelete(Long isDelete,Pageable pageable);
    //快速查询（阶段、状态、类型、优先、阶段停留）
    @Query(nativeQuery = true, value = "select s.*,C2.*,CONTACT.*,U.* from TB_SALE_OPP s left join TB_CUSTOMER C2 on s.CUS_ID = C2.CUS_ID " +
            "left join TB_CONTACTS CONTACT on s.联系人ID = CONTACT.COTS_ID left join TB_USER U on C2.USER_ID = U.USER_ID" +
            " where s.IS_DELETE = ?1 and s.STAGE like ?2 or " +
            "s.OPP_STATUS like ?3 or s.CLASSIFICATION like ?4 or s.PRIORITY like ?5 and s.OPP_THEME like ?6 " +
            "or (sysdate - s.stage_start_time) between ?7 and ?8  ORDER BY ?#{#pageable}")
    public List<SaleOppEntity> findAllByIsDeleteWhere(Long isDelete, String stage,
               String oppStatus, String classification, String priority, String oppTheme, Long min, Long max, Pageable pageable);

    public long countAllByTbUserByUserIdAndIsDelete(UserEntity userEntity,Long isDelete);

    @Query(nativeQuery = true, value = "select count(*) from TB_SALE_OPP where to_number(to_char(CREATE_TIME,'yyyy'))=?1 " +
            "and to_number(to_char(CREATE_TIME,'MM'))=?2 and is_delete = 0")
    public long countAllByMonth(int year, int month);
}
